package com.nccsjz.back.cardbuy.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.nccsjz.base.BaseDAO;
import com.nccsjz.pojo.CardBuy;
import com.nccsjz.utils.Page;

/**
 * CardBuyDAO类用于处理与集体购卡相关的数据访问操作
 * 
 * @author Administrator
 * 
 */
public class CardBuyDAO extends BaseDAO {

	/**
	 * 实现父类的带参构造
	 * 
	 * @param con
	 */
	public CardBuyDAO(Connection con) {
		super(con);
		// TODO Auto-generated constructor stub
	}

	/**
	 * insertCardBuy方法用于插入集体购卡信息
	 * 
	 * @param cardbuy
	 *            CardBuy
	 * @throws SQLException
	 */
	public void insertCardBuy(CardBuy cardbuy) throws SQLException {
		try {
			String sql = "insert into CARDBUY_register (COMPANY,LINKMAN,"
					+ "PHONE,REMARK) values (?,?,?,?)";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, cardbuy.getCompany());
			pstmt.setString(2, cardbuy.getLinkman());
			pstmt.setString(3, cardbuy.getPhone());
			pstmt.setString(4, cardbuy.getRemark());
			pstmt.executeUpdate();
		} finally {
			closeAll(null, pstmt);
		}
	}

	/**
	 * updateCardBuy方法用于更新集体购卡信息
	 * 
	 * @param cardbuy
	 *            CardBuy
	 * @throws SQLException
	 */
	public void updateCardBuy(CardBuy cardbuy) throws SQLException {
		try {
			String sql = "update CARDBUY_REGISTER set COMPANY = ?,LINKMAN = ?, "
					+ " PHONE = ?,REMARK = ?, STATE = ? where REGID = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, cardbuy.getCompany());
			pstmt.setString(2, cardbuy.getLinkman());
			pstmt.setString(3, cardbuy.getPhone());
			pstmt.setString(4, cardbuy.getRemark());
			pstmt.setString(5, String.valueOf(cardbuy.getState()));
			pstmt.setLong(6, cardbuy.getRegid());
			pstmt.executeUpdate();
		} finally {
			closeAll(null, pstmt);
		}
	}

	/**
	 * getCardBuyById方法根据主键获取集体购卡信息
	 * 
	 * @param id
	 *            Long
	 * @return
	 * @throws SQLException
	 */
	public CardBuy getCardBuyById(Long id) throws SQLException {
		CardBuy cardbuy = null;
		try {
			String sql = "select REGID,COMPANY,LINKMAN,PHONE,REMARK,REGDATE,STATE "
					+ " from CARDBUY_REGISTER where REGID = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				cardbuy = new CardBuy(rs.getLong(1), rs.getString(2), rs
						.getString(3), rs.getString(4), rs.getString(5), rs
						.getDate(6), (char) rs.getString(7).charAt(0));
			}
		} finally {
			closeAll(rs, pstmt);
		}
		return cardbuy;
	}

	/**
	 * getCardBuysByState根据处理状态和集体购卡注册日期查询集体购卡信息
	 * 
	 * @param cardbuy
	 * @param page
	 * @throws SQLException
	 */
	public void getCardBuysByState(String state, Page page)
			throws SQLException {
		List<CardBuy> result = new ArrayList<CardBuy>();
		String sql = "select u.REGID,u.COMPANY,u.LINKMAN,u.PHONE,u.REMARK,"
				+ "u.REGDATE,u.STATE from (select row_number() over(order by REGID desc) rn,REGID,COMPANY,LINKMAN,"
				+ " PHONE,REMARK,REGDATE,STATE from CARDBUY_REGISTER where STATE = ? order by REGID desc)" 
				+ " u where u.rn between ? and ?";	
		try {
			// 查询数据
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, state);
			pstmt.setLong(2, page.getStartIndex());
			pstmt.setLong(3, page.getEndIndex());			
			rs = pstmt.executeQuery();
			while (rs.next()) {
				CardBuy cb = new CardBuy(rs.getLong(1), rs.getString(2),
						rs.getString(3), rs.getString(4), rs.getString(5), rs
								.getDate(6), rs.getString(7).charAt(0));
				result.add(cb);
			}
			page.setData(result);			
		} finally {
			closeAll(rs,pstmt);
		}
	}
	
	/**
	 * getRecordCountByState根据状态获得记录总数
	 * 
	 * @param state
	 * @param page
	 * @return
	 * @throws SQLException 
	 */
	public Long getRecordCountByState(String state) throws SQLException{
		Long count = 0L;
		String sql = "select count(*) from CARDBUY_REGISTER where STATE = ?";
		try{
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, state);
			rs = pstmt.executeQuery();
			rs.next();
			count = rs.getLong(1);
		}finally{
			closeAll(rs,pstmt);
		}
		return count;
	}
	
	
	
	
	/**
	 * deleteCardBuyId方法根据主键删除指定集体购卡信息
	 * 
	 * @param id
	 * @throws SQLException 
	 */
	public void deleteCardBuyById(Long id) throws SQLException{
		try{
			String sql = "delete from CARDBUY_REGISTER where REGID = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, id);
			pstmt.executeUpdate();
		}finally{
			closeAll(null,pstmt);
		}
	}

}
